<!DOCTYPE html>



  


<html class="theme-next muse use-motion" lang="zh-Hans">
<head><meta name="generator" content="Hexo 3.9.0">
  <meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<meta name="theme-color" content="#222">









<meta http-equiv="Cache-Control" content="no-transform">
<meta http-equiv="Cache-Control" content="no-siteapp">
















  
  
  <link href="/blog/lib/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css">







<link href="/blog/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css">

<link href="/blog/css/main.css?v=5.1.4" rel="stylesheet" type="text/css">


  <link rel="apple-touch-icon" sizes="180x180" href="/blog/images/apple-touch-icon-next.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="32x32" href="/blog/images/favicon-32x32-next.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="16x16" href="/blog/images/favicon-16x16-next.png?v=5.1.4">


  <link rel="mask-icon" href="/blog/images/logo.svg?v=5.1.4" color="#222">





  <meta name="keywords" content="Hexo, NexT">










<meta property="og:type" content="website">
<meta property="og:title" content="Hexo">
<meta property="og:url" content="https://gitee.com/blghost/blog.git/index.html">
<meta property="og:site_name" content="Hexo">
<meta property="og:locale" content="zh-Hans">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="Hexo">



<script type="text/javascript" id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/blog/',
    scheme: 'Muse',
    version: '5.1.4',
    sidebar: {"position":"left","display":"post","offset":12,"b2t":false,"scrollpercent":false,"onmobile":false},
    fancybox: true,
    tabs: true,
    motion: {"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},
    duoshuo: {
      userId: '0',
      author: '博主'
    },
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    }
  };
</script>



  <link rel="canonical" href="https://gitee.com/blghost/blog.git/">





  <title>Hexo</title>
  








</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-Hans">

  
  
    
  

  <div class="container sidebar-position-left 
  page-home">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta ">
    

    <div class="custom-logo-site-title">
      <a href="/blog/" class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">Hexo</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
      
        <p class="site-subtitle"></p>
      
  </div>

  <div class="site-nav-toggle">
    <button>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/blog/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-home"></i> <br>
            
            首页
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/blog/archives/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-archive"></i> <br>
            
            归档
          </a>
        </li>
      

      
    </ul>
  

  
</nav>



 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            
  <section id="posts" class="posts-expand">
    
      

  

  
  
  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="https://gitee.com/blghost/blog.git/blog/2019/10/24/MySQL函数及其利用/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="Blackghost">
      <meta itemprop="description" content>
      <meta itemprop="image" content="/blog/images/avatar.gif">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Hexo">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
                
                <a class="post-title-link" href="/blog/2019/10/24/MySQL函数及其利用/" itemprop="url">MySQL函数及其利用</a></h1>
        

        <div class="post-meta">
          <span class="post-time">
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              
              <time title="创建于" itemprop="dateCreated datePublished" datetime="2019-10-24T18:03:00+08:00">
                2019-10-24
              </time>
            

            

            
          </span>

          

          
            
          

          
          

          

          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        
          
            <h2 id="背景"><a href="#背景" class="headerlink" title="背景"></a>背景</h2><p>1、学习数据库自带函数的功能与用法（思考在什么情况下可以执行命令）</p>
<p>2、将所有涉及的函数进行测试并举例说明其用法并形成报告扩展学习：针对自己所选数据库，尝试执行系统命令，自己构造所需环境。</p>
<h2 id="前提条件"><a href="#前提条件" class="headerlink" title="前提条件"></a>前提条件</h2><ul>
<li>环境配置如下：win2003+MySQL。</li>
</ul>
<h2 id="MySQL函数"><a href="#MySQL函数" class="headerlink" title="MySQL函数"></a>MySQL函数</h2><h3 id="字符串型"><a href="#字符串型" class="headerlink" title="字符串型"></a>字符串型</h3><h4 id="ASCII函数"><a href="#ASCII函数" class="headerlink" title="ASCII函数"></a>ASCII函数</h4><ul>
<li>返回字符串s的第一个字符的ASCII码。ascii（s）</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select ascii(&apos; &apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571918336041.png" alt="1571918336041"></p>
<h4 id="长度计算函数"><a href="#长度计算函数" class="headerlink" title="长度计算函数"></a>长度计算函数</h4><p>返回字符串s的字符数</p>
<ul>
<li>CHAR_LENGTH（s）</li>
<li>CHARACTER_LENGTH（s）  【不管汉字、数字还是英文都算1个字符】</li>
<li>LENGTH（s）【utf8字符编码下，一个汉字算三个字符，一个数字或一个字母算一个字符。其他编码情况下，一个汉字算两个字符。一个数字或字母算一个字符。】</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">Select length(&quot;aaa&quot;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571919876363.png" alt="1571919876363"></p>
<h4 id="合并字符串函数"><a href="#合并字符串函数" class="headerlink" title="合并字符串函数"></a>合并字符串函数</h4><p>把s1，s2等多个字符串合并为一个字符串，并返回结果出去。</p>
<ul>
<li>CONCAT(s1,s2,…sn)</li>
<li>CONCAT_WS(x,s1,s2,….sn)  多个字符串用x加以分割。</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select concat(&apos;sql&apos;,&apos;123&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571920526176.png" alt="1571920526176"></p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571920577704.png" alt="1571920577704"></p>
<h4 id="查找多个字符位置"><a href="#查找多个字符位置" class="headerlink" title="查找多个字符位置"></a>查找多个字符位置</h4><ul>
<li>返回在s1，s2…sn之间查找s字符串的所在位置。FIELD(s,s1,s2….sn)；方便单个单个输入。</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select field(&quot;1&quot;,&apos;1&apos;,&apos;2&apos;,&apos;3&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571923406964.png" alt="1571923406964"></p>
<h4 id="查找单个字符位置"><a href="#查找单个字符位置" class="headerlink" title="查找单个字符位置"></a>查找单个字符位置</h4><ul>
<li>返回在字符串s2中与s1匹配的字符串的位置。find_in_set(s1,s2)；输入一整个。</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select find_in_set(&apos;1&apos;,&apos;4,1,6,8,4,5&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571923653334.png" alt="1571923653334"></p>
<h4 id="格式化数字函数"><a href="#格式化数字函数" class="headerlink" title="格式化数字函数"></a>格式化数字函数</h4><ul>
<li>函数将数字x进行格式化；“####.##”将x保留到小数点后n位，最后一位四舍五入。format(x,n)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select format(25000.0346,2);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571925224450.png" alt="1571925224450"></p>
<h4 id="替换字符串函数"><a href="#替换字符串函数" class="headerlink" title="替换字符串函数"></a>替换字符串函数</h4><ul>
<li>字符串s2替换s1的x位置开始长度为len的字符串。insert(s1,x,len,s2)</li>
<li>将字符串s2替代字符串s中的字符串s1。replace（s,s1,s2）</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select insert(&apos;aaa&apos;,2,2,&apos;bb&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571927088779.png" alt="1571927088779"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select replace(&quot;abc&quot;,&apos;a&apos;,&apos;x&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572066704083.png" alt="1572066704083"></p>
<h4 id="查找字符串函数"><a href="#查找字符串函数" class="headerlink" title="查找字符串函数"></a>查找字符串函数</h4><ul>
<li>从字符串s中获取s1的开始位置。locate（s1,s）</li>
<li>原理同上。position(s in s1)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select locate(&quot;3&quot;,&quot;4136874&quot;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572064703956.png" alt="1572064703956"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select position(&quot;a&quot; in &quot;bac&quot;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572066113006.png" alt="1572066113006"></p>
<h4 id="转换为小写函数"><a href="#转换为小写函数" class="headerlink" title="转换为小写函数"></a>转换为小写函数</h4><ul>
<li>lcase（s）</li>
<li>lower（s）将字符串s的所有字母变成小写字母</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select lcase(&quot;RUNNN&quot;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572064938939.png" alt="1572064938939"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select lower(&quot;RYUNNN&quot;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572064992325.png" alt="1572064992325"></p>
<h4 id="转换为大写函数"><a href="#转换为大写函数" class="headerlink" title="转换为大写函数"></a>转换为大写函数</h4><p>将字符串s转换为大写</p>
<ul>
<li>ucase(s)</li>
<li>upper(s)</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select ucase(&quot;aa&quot;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572068612303.png" alt="1572068612303"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select upper(&quot;bb&quot;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572068627713.png" alt="1572068627713"></p>
<h4 id="返回字符串函数"><a href="#返回字符串函数" class="headerlink" title="返回字符串函数"></a>返回字符串函数</h4><ul>
<li>返回字符串s的前n个字符。left(s,n)</li>
<li>返回字符串s的后n个字符。right(s,n)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select left(&quot;bug123&quot;,3);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572065111522.png" alt="1572065111522"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select right(&quot;123bug&quot;,3);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572065848068.png" alt="1572065848068"></p>
<h4 id="填充字符串函数"><a href="#填充字符串函数" class="headerlink" title="填充字符串函数"></a>填充字符串函数</h4><ul>
<li>在字符串s1的开始处填充字符串s2，使字符串长度达到len。lpad(s1,len,s2);</li>
<li>在字符串s1的结尾处填充字符串s2，使字符串长度达到len。rpad(s1,len,s2);</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select lpad(&quot;abc&quot;,6,&quot;aaa&quot;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572065323113.png" alt="1572065323113"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select rpad(&apos;ac&apos;,6,&apos;tion&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572066862198.png" alt="1572066862198"></p>
<h4 id="去除空格函数"><a href="#去除空格函数" class="headerlink" title="去除空格函数"></a>去除空格函数</h4><ul>
<li>去掉字符串s开始处的空格。ltrim(s)</li>
<li>去掉字符串s结尾处的空格。rtrim(s)</li>
<li>去掉字符串s开头和结尾处的空格。trim(s)。</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select ltrim(&quot;   ccc&quot;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572065656366.png" alt="1572065656366"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select rtrim(&quot;ccc   &quot;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572067146818.png" alt="1572067146818"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select trim(&quot;  cc  &quot;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572068501292.png" alt="1572068501292"></p>
<h4 id="截取字符串函数"><a href="#截取字符串函数" class="headerlink" title="截取字符串函数"></a>截取字符串函数</h4><ul>
<li>从字符串s的n位置截取长度为len的字符串，同【SUBSTRING/SUBSTR / mid(s,n,len)】</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select mid(&quot;aababahadca&quot;,4,3);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572065984104.png" alt="1572065984104"></p>
<h4 id="重复复制字符串"><a href="#重复复制字符串" class="headerlink" title="重复复制字符串"></a>重复复制字符串</h4><ul>
<li>将字符串s重复n次。repeat(s,n)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select repeat(&quot;aa&quot;,3);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572066329013.png" alt="1572066329013"></p>
<h4 id="返回空格函数"><a href="#返回空格函数" class="headerlink" title="返回空格函数"></a>返回空格函数</h4><ul>
<li>返回n个空格。space（n）</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select space(10);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572067242997.png" alt="1572067242997"></p>
<h4 id="字符串比较函数"><a href="#字符串比较函数" class="headerlink" title="字符串比较函数"></a>字符串比较函数</h4><ul>
<li>比较字符串s1和s2，如果s1与s2相等返回0，如果s1&gt;s2 返回1。如果s1&lt;s2 返回 -1。strcmp(s1,s2)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select strcmp(&quot;aa&quot;,&quot;bb&quot;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572067624509.png" alt="1572067624509"></p>
<h4 id="分割字符串函数"><a href="#分割字符串函数" class="headerlink" title="分割字符串函数"></a>分割字符串函数</h4><p>返回从字符串s的第num个出现的分割符x之后的字符串。substring_index(s,x,num)</p>
<ul>
<li>如果num是正数，返回num个字符左边的字符串。</li>
<li>如果num是负数，返回num的绝对值（从右边数）个字符右边的字符串。</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select substring_index(&apos;a-b&apos;,&quot;-&quot;,1);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572068147384.png" alt="1572068147384"></p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572068164825.png" alt="1572068164825"></p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572068227181.png" alt="1572068227181"></p>
<h3 id="数值型"><a href="#数值型" class="headerlink" title="数值型"></a>数值型</h3><h4 id="绝对值函数"><a href="#绝对值函数" class="headerlink" title="绝对值函数"></a>绝对值函数</h4><ul>
<li>返回x的绝对值。abs(x)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select abs(-1);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572085653778.png" alt="1572085653778"></p>
<h4 id="反余弦函数"><a href="#反余弦函数" class="headerlink" title="反余弦函数"></a>反余弦函数</h4><ul>
<li>求x的反余弦值。acos(x)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select acos(0.25);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572085789090.png" alt="1572085789090"></p>
<h4 id="反正弦函数"><a href="#反正弦函数" class="headerlink" title="反正弦函数"></a>反正弦函数</h4><ul>
<li>求x的反正弦值。asin(x)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select asin(0.25);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572086207817.png" alt="1572086207817"></p>
<h4 id="反正切函数"><a href="#反正切函数" class="headerlink" title="反正切函数"></a>反正切函数</h4><ul>
<li>求x的反正切值。atan(x)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select atan(2.5);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572086557657.png" alt="1572086557657"></p>
<ul>
<li>求x反正切值，参数是弧度。atan2(n,m)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select atan2(-0.8,2);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572150131302.png" alt="1572150131302"></p>
<h4 id="平均值函数"><a href="#平均值函数" class="headerlink" title="平均值函数"></a>平均值函数</h4><ul>
<li>返回一个表达式的平均值，expression是一个字段。avg(expression)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select avg(alexa) from websites;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572243526558.png" alt="1572243526558"></p>
<h4 id="最小整数函数"><a href="#最小整数函数" class="headerlink" title="最小整数函数"></a>最小整数函数</h4><p>返回大于或等于x的最小整数。</p>
<ul>
<li>ceil(x)</li>
<li>ceiling(x)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select ceil(1.5);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572243874860.png" alt="1572243874860"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select ceiling(1.5);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572243903812.png" alt="1572243903812"></p>
<h4 id="最大整数函数"><a href="#最大整数函数" class="headerlink" title="最大整数函数"></a>最大整数函数</h4><ul>
<li>返回小于或等于x的最大整数。floor(x)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select floor(1.5);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572249153923.png" alt="1572249153923"></p>
<h4 id="余弦函数"><a href="#余弦函数" class="headerlink" title="余弦函数"></a>余弦函数</h4><ul>
<li>求余弦值（参数是弧度）。cos(x)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select cos(2);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572244342671.png" alt="1572244342671"></p>
<h4 id="余切函数"><a href="#余切函数" class="headerlink" title="余切函数"></a>余切函数</h4><ul>
<li>求余切值（参数是弧度）。cot(x)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select cot(2);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572244516352.png" alt="1572244516352"></p>
<h4 id="统计记录"><a href="#统计记录" class="headerlink" title="统计记录"></a>统计记录</h4><ul>
<li>返回查询的记录总数，参数为一个字段或者*号。count(expression)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select count(id) from websites;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572245067511.png" alt="1572245067511"></p>
<h4 id="弧度转角度"><a href="#弧度转角度" class="headerlink" title="弧度转角度"></a>弧度转角度</h4><ul>
<li>将弧度转角度。DEGREES(x)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select degrees(3.1415926);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572247642656.png" alt="1572247642656"></p>
<h4 id="整除函数"><a href="#整除函数" class="headerlink" title="整除函数"></a>整除函数</h4><ul>
<li>n为被除数，m为除数</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select 10 div 5;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572248088092.png" alt="1572248088092"></p>
<h4 id="e的x次方"><a href="#e的x次方" class="headerlink" title="e的x次方"></a>e的x次方</h4><ul>
<li>返回e的x次方。exp(x)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select exp(3);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572248962039.png" alt="1572248962039"></p>
<h4 id="列表最大值"><a href="#列表最大值" class="headerlink" title="列表最大值"></a>列表最大值</h4><ul>
<li>返回列表中的最大值。greatest(13,22,41)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select greatest(13,22,41);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572249329035.png" alt="1572249329035"></p>
<h4 id="列表最小值"><a href="#列表最小值" class="headerlink" title="列表最小值"></a>列表最小值</h4><ul>
<li>返回列表中的最小值。least（13，22，41）</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select least(13,22,41);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572249451325.png" alt="1572249451325"></p>
<h4 id="自然对数函数"><a href="#自然对数函数" class="headerlink" title="自然对数函数"></a>自然对数函数</h4><ul>
<li>返回数字x的自然对数。ln(x)</li>
<li>返回自然对数，以e为底的对数。log(x)</li>
<li>返回以10为底的对数。log10(100)</li>
<li>返回以2为底的对数。log2(8)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select ln(2);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572256910711.png" alt="1572256910711"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select log(20.085536923188);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572425002692.png" alt="1572425002692"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select log10(100);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572425057962.png" alt="1572425057962"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select log2(8);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572425225851.png" alt="1572425225851"></p>
<h4 id="最大值函数"><a href="#最大值函数" class="headerlink" title="最大值函数"></a>最大值函数</h4><ul>
<li>返回字段x中的最大值。max(x)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select max(alexa) from websites;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572426463247.png" alt="1572426463247"></p>
<h4 id="最小值函数"><a href="#最小值函数" class="headerlink" title="最小值函数"></a>最小值函数</h4><ul>
<li>返回字段x中的最小值。min(x)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select min(alexa) from websites;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572426533618.png" alt="1572426533618"></p>
<h4 id="求余函数"><a href="#求余函数" class="headerlink" title="求余函数"></a>求余函数</h4><ul>
<li>返回x除以y以后的余数。mod(5,2)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select mod(5,2);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572426825178.png" alt="1572426825178"></p>
<h4 id="圆周率函数"><a href="#圆周率函数" class="headerlink" title="圆周率函数"></a>圆周率函数</h4><ul>
<li>返回圆周率函数。pi()</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select pi();</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572511901871.png" alt="1572511901871"></p>
<h4 id="求次方函数"><a href="#求次方函数" class="headerlink" title="求次方函数"></a>求次方函数</h4><p>​    返回x的y次方</p>
<ul>
<li>pow(x,y)</li>
<li>power(x,y)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select pow(2,3);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572512279462.png" alt="1572512279462"></p>
<h4 id="转弧度函数"><a href="#转弧度函数" class="headerlink" title="转弧度函数"></a>转弧度函数</h4><ul>
<li>将角度转换为弧度。radians(x)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select radians(180);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572512512155.png" alt="1572512512155"></p>
<h4 id="随机函数"><a href="#随机函数" class="headerlink" title="随机函数"></a>随机函数</h4><ul>
<li>返回0到1的随机数。rand()</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select rand();</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572512733708.png" alt="1572512733708"></p>
<h4 id="最近的整数"><a href="#最近的整数" class="headerlink" title="最近的整数"></a>最近的整数</h4><ul>
<li>返回离x最近的整数。round(x)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select round(1.23);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572512907630.png" alt="1572512907630"></p>
<h4 id="返回x的函数"><a href="#返回x的函数" class="headerlink" title="返回x的函数"></a>返回x的函数</h4><ul>
<li>x是负数、0、正数分别返回，-1、0、1。sign(x)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select sign(-10);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572513078456.png" alt="1572513078456"></p>
<h4 id="正弦函数"><a href="#正弦函数" class="headerlink" title="正弦函数"></a>正弦函数</h4><ul>
<li>求正弦值函数（参数是弧度）。sin(x)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select sin(radians(30));</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572513216000.png" alt="1572513216000"></p>
<h4 id="平方根函数"><a href="#平方根函数" class="headerlink" title="平方根函数"></a>平方根函数</h4><ul>
<li>返回x的平方根。sqrt(x)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select sqrt(9);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572513322697.png" alt="1572513322697"></p>
<h4 id="字段数总和"><a href="#字段数总和" class="headerlink" title="字段数总和"></a>字段数总和</h4><ul>
<li>返回指定字段的总和。sum(expression)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select sum(alexa) from websites;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572513451340.png" alt="1572513451340"></p>
<h4 id="正切函数"><a href="#正切函数" class="headerlink" title="正切函数"></a>正切函数</h4><ul>
<li>求正切函数（参数是弧度）。TAN（x）</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select tan(1.75);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572513574056.png" alt="1572513574056"></p>
<h4 id="保留小数点函数"><a href="#保留小数点函数" class="headerlink" title="保留小数点函数"></a>保留小数点函数</h4><ul>
<li>返回数值x保留多少y位小数点。truncate(x,y)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select truncate(1.23456,3);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572513740031.png" alt="1572513740031"></p>
<h3 id="日期类型"><a href="#日期类型" class="headerlink" title="日期类型"></a>日期类型</h3><h4 id="加天数函数"><a href="#加天数函数" class="headerlink" title="加天数函数"></a>加天数函数</h4><ul>
<li>计算起始日期d加上n天的日期。addDate(d,n)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select adddate(&quot;2019-11-1&quot;,INTERVAL 10 DAY);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572594559005.png" alt="1572594559005"></p>
<h4 id="加时间函数"><a href="#加时间函数" class="headerlink" title="加时间函数"></a>加时间函数</h4><ul>
<li>时间t加上n秒的时间。addTime(t,n)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select addTime(&apos;2019-11-1 11:11:11&apos;,5);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572594846610.png" alt="1572594846610"></p>
<h4 id="当前日期"><a href="#当前日期" class="headerlink" title="当前日期"></a>当前日期</h4><p>返回当前日期</p>
<ul>
<li>curdate()</li>
<li>current_date()</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select curdate();</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572595076152.png" alt="1572595076152"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select current_date();</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572595110739.png" alt="1572595110739"></p>
<h4 id="当前时间"><a href="#当前时间" class="headerlink" title="当前时间"></a>当前时间</h4><p>返回当前时间</p>
<ul>
<li>current_time()</li>
<li>curtime()</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select current_time();</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572595371073.png" alt="1572595371073"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select curtime();</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572595406062.png" alt="1572595406062"></p>
<h4 id="当前日期和时间"><a href="#当前日期和时间" class="headerlink" title="当前日期和时间"></a>当前日期和时间</h4><ul>
<li>返回当前日期和时间。current_timestamp()</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select current_timestamp();</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572595554860.png" alt="1572595554860"></p>
<h4 id="计算相隔天数"><a href="#计算相隔天数" class="headerlink" title="计算相隔天数"></a>计算相隔天数</h4><ul>
<li>计算日期d1-&gt;d2之间相隔的天数。datediff(d1,d2)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select datediff(&apos;2012-1-1&apos;,&apos;2013-1-1&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572596545176.png" alt="1572596545176"></p>
<h4 id="表达式显示日期"><a href="#表达式显示日期" class="headerlink" title="表达式显示日期"></a>表达式显示日期</h4><ul>
<li>按表达式f的要求显示日期d。date_format(d,f)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select date_format(&apos;2011-11-11 11:11:11&apos;,&apos;%d-%m-%Y %r&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572597423927.png" alt="1572597423927"></p>
<h4 id="显示日期部分"><a href="#显示日期部分" class="headerlink" title="显示日期部分"></a>显示日期部分</h4><ul>
<li>返回日期值d的日期部分。day(d)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select day(&apos;2017-6-15&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572611679828.png" alt="1572611679828"></p>
<h4 id="显示星期函数"><a href="#显示星期函数" class="headerlink" title="显示星期函数"></a>显示星期函数</h4><ul>
<li>返回日期d是星期几。dayname(d)</li>
<li>日期d今天是星期几，1星期日，2星期一以此类推。dayofweek(d)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select dayname(&apos;2019-11-1&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572612179011.png" alt="1572612179011"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select dayofweek(&apos;2019-11-1&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572612690723.png" alt="1572612690723"></p>
<h4 id="显示本月的天数"><a href="#显示本月的天数" class="headerlink" title="显示本月的天数"></a>显示本月的天数</h4><ul>
<li>计算日期d是本月的第几天。dayofmonth(d)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select dayofmonth(&apos;2017-11-6&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572612495288.png" alt="1572612495288"></p>
<h4 id="显示到本年的天数"><a href="#显示到本年的天数" class="headerlink" title="显示到本年的天数"></a>显示到本年的天数</h4><ul>
<li>计算日期d是本年的第几天。dayofyear(‘2016-11-1’);</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select dayofyear(&apos;2016-11-1&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572613190270.png" alt="1572613190270"></p>
<h3 id="高级函数"><a href="#高级函数" class="headerlink" title="高级函数"></a>高级函数</h3><h4 id="二进制编码"><a href="#二进制编码" class="headerlink" title="二进制编码"></a>二进制编码</h4><ul>
<li>返回x的二进制编码。bin(x)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select bin(15);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572614764654.png" alt="1572614764654"></p>
<h4 id="字符串转二进制"><a href="#字符串转二进制" class="headerlink" title="字符串转二进制"></a>字符串转二进制</h4><ul>
<li>将字符串s转换为二进制字符串。binary(s)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select binary(&apos;runmb&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572615667584.png" alt="1572615667584"></p>
<h4 id="条件函数"><a href="#条件函数" class="headerlink" title="条件函数"></a>条件函数</h4><ul>
<li>case表示函数开始，end表示函数结束。如果condition 1成立，则返回result1，如果condition 2成立，则返回result2，如果全部不成立则返回result。当一个成立后，后面就不执行了。</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">select case </span><br><span class="line">when 1 &lt; 0 then &apos;1&gt;0&apos;</span><br><span class="line">when 2 &gt; 0 then &apos;2&gt;0&apos;</span><br><span class="line">else &apos;3&gt;0&apos;</span><br><span class="line">end;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572859091222.png" alt="1572859091222"></p>
<h4 id="转换数据"><a href="#转换数据" class="headerlink" title="转换数据"></a>转换数据</h4><ul>
<li>转换数据类型。cast(x as type)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select cast(&apos;2017-08-29&apos; as date);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572860557822.png" alt="1572860557822"></p>
<h4 id="非空函数"><a href="#非空函数" class="headerlink" title="非空函数"></a>非空函数</h4><ul>
<li>返回参数中的第一个非空表达式（从左到右）。coalesce（expr1,expr2,expr3）</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select coalesce(null,null,&apos;123&apos;,null,&apos;456&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572861635132.png" alt="1572861635132"></p>
<h4 id="服务器连接数"><a href="#服务器连接数" class="headerlink" title="服务器连接数"></a>服务器连接数</h4><ul>
<li>返回服务器的连接数。connection_id()</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select connection_id();</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572862035170.png" alt="1572862035170"></p>
<h4 id="进制数转换"><a href="#进制数转换" class="headerlink" title="进制数转换"></a>进制数转换</h4><ul>
<li>返回f1进制数变成f2进制数。conv(x,10,2);</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select conv(15,10,2);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572862452239.png" alt="1572862452239"></p>
<h4 id="当前用户"><a href="#当前用户" class="headerlink" title="当前用户"></a>当前用户</h4><p>返回当前用户。</p>
<ul>
<li>current_user()</li>
<li>session_user()</li>
<li>system_user()</li>
<li>user()</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select current_user();</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572862970285.png" alt="1572862970285"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select session_user();</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1573025960234.png" alt="1573025960234"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select system_user();</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1573026020175.png" alt="1573026020175"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select user();</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1573026126803.png" alt="1573026126803"></p>
<h4 id="当前数据库"><a href="#当前数据库" class="headerlink" title="当前数据库"></a>当前数据库</h4><ul>
<li>返回当前数据库名。database()</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select database();</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572863214780.png" alt="1572863214780"></p>
<h4 id="如果函数"><a href="#如果函数" class="headerlink" title="如果函数"></a>如果函数</h4><ul>
<li>如果表达式expr成立，返回结果v1；否则返回结果v2。if(expr,v1,v2)</li>
</ul>
<p>实例:</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select if(1&gt;0,&apos;1&apos;,&apos;0&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572864200577.png" alt="1572864200577"></p>
<h4 id="是否为空函数"><a href="#是否为空函数" class="headerlink" title="是否为空函数"></a>是否为空函数</h4><ul>
<li>如果v1的值不为Null,则返回v1,否则返回v2。ifnull(v1,v2)</li>
<li>判断表达式是否为null。isnull(expression)</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select ifnull(&quot;1&quot;,&apos;123&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1572864439679.png" alt="1572864439679"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select isnull(alexa);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1573026263869.png" alt="1573026263869"></p>
<h4 id="最后生成的Auto-increment值"><a href="#最后生成的Auto-increment值" class="headerlink" title="最后生成的Auto_increment值"></a>最后生成的Auto_increment值</h4><ul>
<li>返回最近生成的auto_increment值。last_insert_id()</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select last_insert_id();</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1573025615288.png" alt="1573025615288"></p>
<h4 id="显示数据库版本"><a href="#显示数据库版本" class="headerlink" title="显示数据库版本"></a>显示数据库版本</h4><ul>
<li>返回数据库的版本号。</li>
</ul>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select version();</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1573026304666.png" alt="1573026304666"></p>

          
        
      
    </div>
    
    
    

    

    

    

    <footer class="post-footer">
      

      

      

      
      
        <div class="post-eof"></div>
      
    </footer>
  </div>
  
  
  
  </article>


    
      

  

  
  
  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="https://gitee.com/blghost/blog.git/blog/2019/10/07/数据库系统表相关学习/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="Blackghost">
      <meta itemprop="description" content>
      <meta itemprop="image" content="/blog/images/avatar.gif">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Hexo">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
                
                <a class="post-title-link" href="/blog/2019/10/07/数据库系统表相关学习/" itemprop="url">数据库系统表相关学习</a></h1>
        

        <div class="post-meta">
          <span class="post-time">
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              
              <time title="创建于" itemprop="dateCreated datePublished" datetime="2019-10-07T13:12:00+08:00">
                2019-10-07
              </time>
            

            

            
          </span>

          

          
            
          

          
          

          

          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        
          
            <h2 id="目的"><a href="#目的" class="headerlink" title="目的"></a>目的</h2><ol>
<li>如何利用数据库的功能读写文件，需要什么样的条件才可以读写</li>
<li>学习数据库系统表的功能，如何利用SQL语句查询库名、表名、字段名、内容以及当前用户等基本信息。</li>
<li>尝试查询出用户的hash，并使用hashcat来对获取的hash进行暴力破解</li>
</ol>
<h2 id="MySQL读写文件"><a href="#MySQL读写文件" class="headerlink" title="MySQL读写文件"></a>MySQL读写文件</h2><h3 id="读文件："><a href="#读文件：" class="headerlink" title="读文件："></a>读文件：</h3><h4 id="背景知识"><a href="#背景知识" class="headerlink" title="背景知识"></a>背景知识</h4><p>MySQL有2种读取文件的方法：load_file()、load data infile（在对文件进行读写的时候需要相对应的操作权限，否则无法对文件进行读写，load_file和load data infile，<strong>需要设置 secure_file_priv不为NULL</strong>）</p>
<p>可以使用show 命令来查看secure_file_priv是否为NULL，具体命令如下：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show variables like ‘%secure%’;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570512801339.png" alt="1570512801339"></p>
<p>可以通过mysql.ini文件中修改其属性，修改完成后需要重启MySQL；</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570513200148.png" alt="1570513200148"></p>
<h4 id="Load-file函数读取文件"><a href="#Load-file函数读取文件" class="headerlink" title="Load_file函数读取文件"></a>Load_file函数读取文件</h4><p>用法：load_file(path+file_name[地址+文件名])。</p>
<p>解析：读取文件并返回文件内容为字符串。如果该文件不存在或无法读取，函数返回NULL。</p>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select load_file(&apos;c:\\test.txt&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570513882262.png" alt="1570513882262"></p>
<p>读取文件成功！</p>
<h4 id="Select-load-data-infile读取文件"><a href="#Select-load-data-infile读取文件" class="headerlink" title="Select load data infile读取文件"></a>Select load data infile读取文件</h4><p>用法：load data infile path+file[地址+文件名] into table table_name[表名]。</p>
<p>解析：首先需要一个跟文件想对应的表，然后才能读取文件并返回文件内容为字符串到指定的表中。如果该文件不存在或无法读取。</p>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">load data infile &apos;c:\\test.txt&apos; into table text;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570514897058.png" alt="1570514897058"></p>
<p>补充：</p>
<ul>
<li>FIELDS TERMINATED BY ‘，’这里指以逗号为分割符来分割导入数据。</li>
<li>IGNORE  1 LINES  这里指忽略第一行。</li>
</ul>
<h3 id="写文件："><a href="#写文件：" class="headerlink" title="写文件："></a>写文件：</h3><p>MySQL提供了一个into outfile函数可以将一条select语句的结果写到MySQL进程所有者拥有的完全读写文件中。</p>
<h4 id="select-into-outfile函数"><a href="#select-into-outfile函数" class="headerlink" title="select into outfile函数"></a>select into outfile函数</h4><p>用法：SELECT ‘文件内容’  into outfile ‘文件地址+文件名’;</p>
<p>解析：返回一条查询结果，在目标地址生成一个文件。</p>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select &apos;&lt;?php phpinfo();?&gt;&apos; into outfile &apos;c:\\1.txt&apos;;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570516699258.png" alt="1570516699258"></p>
<h2 id="MySQL5-7系统库"><a href="#MySQL5-7系统库" class="headerlink" title="MySQL5.7系统库"></a>MySQL5.7系统库</h2><table>
<thead>
<tr>
<th>库名</th>
<th>表数量</th>
</tr>
</thead>
<tbody><tr>
<td>information_schema</td>
<td>61</td>
</tr>
<tr>
<td>MySQL</td>
<td>32</td>
</tr>
<tr>
<td>performance_schema</td>
<td>87</td>
</tr>
<tr>
<td>sys</td>
<td>101</td>
</tr>
</tbody></table>
<h3 id="information-schema数据库"><a href="#information-schema数据库" class="headerlink" title="information_schema数据库"></a>information_schema数据库</h3><p>information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。</p>
<p><strong>什么是元数据呢？</strong></p>
<p>元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等，有时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。</p>
<p>在MySQL中把information_schema看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。在INFORMATION_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。</p>
<p><strong>information_schema数据库部分表说明</strong></p>
<table>
<thead>
<tr>
<th>表名</th>
<th>注释</th>
</tr>
</thead>
<tbody><tr>
<td>SCHEMATA</td>
<td>提供了当前MySQL实例中所有数据库的信息。是show databases的结果取之此表</td>
</tr>
<tr>
<td>TABLES</td>
<td>提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个SCHEMAT、表类型、表引擎、创建时间等信息。是show tables from schemaname的结果取之此表</td>
</tr>
<tr>
<td>COLUMNS</td>
<td>提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemananme.tablename的结果取之此表</td>
</tr>
<tr>
<td>STATISTICS</td>
<td>提供关于表索引的信息。是show index from schemaname.tablename的结果取之此表</td>
</tr>
<tr>
<td>USER_PRIVILEGES</td>
<td>用户权限表，给出了关于全称权限的信息。该信息源自mysql.user授权表。是非标准表</td>
</tr>
<tr>
<td>SCHEMA_PRIVILEGS</td>
<td>方案权限表，给出了关于方案（数据库）权限的信息。该信息来自MySQL.db授权表。是非标准表</td>
</tr>
<tr>
<td>TABLE_PRIVILEGS</td>
<td>表权限表，给出了关于表权限的信息。该信息源自MySQL.tables_priv授权表。是非标准表</td>
</tr>
<tr>
<td>COLUMN_PRIVILEGES</td>
<td>列权限表，给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表</td>
</tr>
<tr>
<td>CHARACTER_SETS</td>
<td>字符集表，提供了mysql实例可用字符集的信息。是show character set结果集取之此表</td>
</tr>
<tr>
<td>COLLATIONS</td>
<td>提供了关于个字符集的对照信息</td>
</tr>
<tr>
<td>COLLATION_CHARACTER_SET_APPLICABILITY</td>
<td>指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。</td>
</tr>
<tr>
<td>TABLE_CONSTRAINTS</td>
<td>描述了存在约束的表。以及表的约束类型</td>
</tr>
<tr>
<td>KEY_COLUM_USAGE</td>
<td>描述了具有约束的键值</td>
</tr>
<tr>
<td>ROUTINES</td>
<td>提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA ROUTINES表的mysql.proc表列</td>
</tr>
<tr>
<td>VIEWS</td>
<td>给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息</td>
</tr>
<tr>
<td>TRIGGERS</td>
<td>提供了关于触发程序的信息。必须有super权限才能查看该表</td>
</tr>
</tbody></table>
<h3 id="performance-schema数据库"><a href="#performance-schema数据库" class="headerlink" title="performance_schema数据库"></a>performance_schema数据库</h3><p>PERFORMANCE_SCHEMA这个功能默认是关闭的。需要设置参数：performance_schema才可以启动该功能，这个参数是静态参数，只能写在my.cnf中不能动态修改。<strong>（主要存储数据库服务器性能参数）</strong></p>
<p><strong>performance_schema数据库部分表说明</strong></p>
<table>
<thead>
<tr>
<th>表名</th>
<th>注释</th>
</tr>
</thead>
<tbody><tr>
<td>setup_table</td>
<td>设置表，配置监控选项</td>
</tr>
<tr>
<td>current_event_table</td>
<td>记录当前那些thread正在发生什么事情</td>
</tr>
<tr>
<td>history_table</td>
<td>发生的各种事件的历史记录表</td>
</tr>
<tr>
<td>summary_table</td>
<td>对各种事件的统计表</td>
</tr>
<tr>
<td>setup_consumers\setup_instruments</td>
<td>描述各个事件，设置哪些事件能够被收集</td>
</tr>
<tr>
<td>setup_instruments</td>
<td>描述这个数据库下的表名以及是否开启监控</td>
</tr>
<tr>
<td>setup_timers</td>
<td>描述监控选项已经采样频率的时间间隔</td>
</tr>
<tr>
<td>threads</td>
<td>监控服务器所有连接</td>
</tr>
<tr>
<td>performance_timers</td>
<td>设置一些监控信息、指定mysql服务可用的监控周期，CYCLE表示按每秒检测2603393034次，目前performance-schema只支持’wait’时间监控，代码树上’wait/‘下的函数都可以监控到</td>
</tr>
</tbody></table>
<h3 id="mysql数据库"><a href="#mysql数据库" class="headerlink" title="mysql数据库"></a>mysql数据库</h3><p>在mysql数据库中，有mysql_install_db脚本初始化权限表，存储权限的表</p>
<table>
<thead>
<tr>
<th>表名</th>
<th>注释</th>
</tr>
</thead>
<tbody><tr>
<td>user</td>
<td>用户列、权限列、安全列、资源控制列</td>
</tr>
<tr>
<td>db</td>
<td>用户列、权限列</td>
</tr>
<tr>
<td></td>
<td></td>
</tr>
</tbody></table>
<h3 id="sys数据库"><a href="#sys数据库" class="headerlink" title="sys数据库"></a>sys数据库</h3><p>sys_config：这是在这个系统库上存在的唯一一个表。</p>
<h2 id="SQL查询系统信息命令"><a href="#SQL查询系统信息命令" class="headerlink" title="SQL查询系统信息命令"></a>SQL查询系统信息命令</h2><p>查询数据库名：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show databases;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570874177412.png" alt="1570874177412"></p>
<p>查询表名：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show tables;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571035759430.png" alt="1571035759430"></p>
<p>查询字段名：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">desc user;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571036670357.png" alt="1571036670357"></p>
<p>查询用户表的内容：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select host,user from user;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571036961521.png" alt="1571036961521"></p>
<p>查看当前用户：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select user();</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571037454088.png" alt="1571037454088"></p>
<p>查看当前版本：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select version();</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571037550503.png" alt="1571037550503"></p>
<h4 id="附加："><a href="#附加：" class="headerlink" title="附加："></a>附加：</h4><p>显示一个用户的权限，显示结果类似于grant命令</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show grants for &apos;root&apos;@&apos;localhost&apos;;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571038702803.png" alt="1571038702803"></p>
<p>显示系统中正在运行的所有进程，就是当前正在执行的查询。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show processlist;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571038802519.png" alt="1571038802519"></p>
<p>显示表的索引：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show index from user;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571038906567.png" alt="1571038906567"></p>
<p>显示最后一个执行的语句所产生的错误、警告和通知</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show warnings</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571039029776.png" alt="1571039029776"></p>
<p>更多查询请看：<a href="https://cloud.tencent.com/developer/article/1340819" target="_blank" rel="noopener">mysql常用表汇总</a></p>
<h2 id="hashcat工具"><a href="#hashcat工具" class="headerlink" title="hashcat工具"></a>hashcat工具</h2><h3 id="背景知识-1"><a href="#背景知识-1" class="headerlink" title="背景知识"></a>背景知识</h3><p>hashcat 是一款世界上最快，最先进的密码恢复实用程序（官网这样写的）。支持不同的平台系统，而且支持GPU来执行破解。</p>
<p>工具的下载地址：<a href="https://hashcat.net/hashcat/" target="_blank" rel="noopener">hashcat</a></p>
<h3 id="使用方法"><a href="#使用方法" class="headerlink" title="使用方法"></a>使用方法</h3><h4 id="常用选项"><a href="#常用选项" class="headerlink" title="常用选项"></a>常用选项</h4><p>在使用hashcat工具中的一些常用的基础选项。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">1. -a 指定要使用的破解模式。</span><br><span class="line">2. -m 指定要破解的hash类型所对应的id[下面有一份完整的hash id对照表]，几乎存在市面上常用的一些散列类型它都支持，而且每个版本更新都会增加一些新的算法。</span><br><span class="line">3. -o 指定破解成功后的hash及所对应的明文密码的存放位置。</span><br><span class="line">4. --force 忽略破解过程中的警告信息，跑单条hash可能需要加上此选项</span><br><span class="line">5. --show 显示已经破解的hash及该hash所对应的明文</span><br><span class="line">6. --increment 启用增量破解模式，你可以利用此模式让hashcat在指定的密码长度范围内执行破解，其实并不建议这么用，因为时间可能会比较长。</span><br><span class="line">7. --increment-min 密码最小长度，后面直接等于一个整数即可，配置increment模式一起使用</span><br><span class="line">8. --increment-max 密码最大长度，同上</span><br><span class="line">9. --outfile-format 指定破解结果的输出格式id，一般默认就可以了，除非有特殊需求。</span><br><span class="line">10. --username 忽略hash文件中的指定的用户名，在破解win和linux系统用户密码hash可能会用到。</span><br><span class="line">11. --remove 删除已被破解成功的hash</span><br><span class="line">12. -r 使用自定义破解规则，比较复杂后期在说。</span><br></pre></td></tr></table></figure>

<h4 id="破解模式"><a href="#破解模式" class="headerlink" title="破解模式"></a>破解模式</h4><p>字典爆破，后面可以连续跟上多个字典文件。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">0 | straight（字典模式）</span><br></pre></td></tr></table></figure>

<p>一种相对智能高效的爆破模式，它的意思是这样的，如果你事先已经明确知道密码中可能包含哪些字符串，你可以把那些字符串事先写到文件中，每行对应一个字符串，然后hashcat会自动根据你所提供的这些字符串，尝试所有可能的组合进行猜解。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">1 | Combination（智能高效爆破）</span><br></pre></td></tr></table></figure>

<p>纯掩码的爆破方式，如果你有需求要大批量爆破hash，可能会用到。这里需要稍微注意的是，你给定的掩码是多少位它就只破解多少位，例如，你给的是7位的掩码，它就只跑7位这么长，而不会从第1位跑到第7位，如果想要自动变长跑，那么就启用increment模式，并且指定密码的最小和最大长度即可。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">3 | Brue-force（掩码模式）</span><br></pre></td></tr></table></figure>

<p>基于字典和掩码配合的爆破模式，它的破解过程其实也比较简单，就是每次从前面的字典取出一个字符串然后和后面的掩码的所有组合进行拼接，直到撞到对应的明文</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">6 | Hybrid Worldlist + Mask（字典+掩码）</span><br></pre></td></tr></table></figure>

<p>基于掩码和字典配合的爆破模式，跟6的过程正好相反，只不过这次它是从前面进行拼接</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">7 | Hybrid Mask + Wordlist（掩码+字典）</span><br></pre></td></tr></table></figure>

<h4 id="掩码样式"><a href="#掩码样式" class="headerlink" title="掩码样式"></a>掩码样式</h4><p>hashcat不同的掩码所表示的具体字符及掩码字符集规则使用样式，掩码是个非常灵活的东西，你可以把它放在任何你想放的位置上，甚至也可以把自己想跑的一些掩码规则都事先放到一个文件中，然后以.hcmask命名，加载就可以让hashcat自动跑了，这样用起来比较方便，省的后期经常需要人为干预。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">l | a-z 纯小写字母</span><br><span class="line">u | A-Z 纯大写字母</span><br><span class="line">d | 0-9 纯数字</span><br><span class="line">h | 0-f 常见小写字母和数字</span><br><span class="line">H | 0-F 常见大写字母和数字</span><br><span class="line">s | !-~ 特殊字符</span><br><span class="line">a | ?l?u?d?s 以上所有字符</span><br><span class="line">b | 0x00-0xff 可能是用来匹配像空格这种密码的</span><br></pre></td></tr></table></figure>

<p>比如，下面的例子：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">1 ?l?l?l?l?l?l?d?d?d 表示6位小写字母组成的密码，注意这里的位置全部都是一一对应的</span><br><span class="line">2 de?l?d?s56pos  表示由de加一位小写字母加一位数字加一位特殊字符后面跟上56pos组成的密码</span><br></pre></td></tr></table></figure>

<p>当然，你也可以自定义字符集规则，注意，可以连续指定多个不同的规则集</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">1 -1， --custom-charset1 | cs| User-defined charset ?1 | -1 ?l?d?u</span><br><span class="line">2 -2， --custom-charset2 | cs| User-defined charset ?2 | -2 ?l?d?s</span><br><span class="line">3 -3， --custom-charset3 | cs| User-defined charset ?3</span><br><span class="line">4 -4， --custom-charset4 | cs| User-defined charset ?4</span><br></pre></td></tr></table></figure>

<p>比如，下面的例子：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">1  -1 ?l?s ?1?1?1?1?1 表示五位由特殊字符和小写字母组成的密码</span><br><span class="line">2  -1 ?d?l -2 ?d?l?u -3 ?l?u ?1?2?3 表示密码的第一位可能是小写字母或者数字，第二位可能是大小写</span><br></pre></td></tr></table></figure>

<h4 id="不同破解模式下的具体应用场景"><a href="#不同破解模式下的具体应用场景" class="headerlink" title="不同破解模式下的具体应用场景"></a>不同破解模式下的具体应用场景</h4><p>用的时候需要稍微注意下语句格式（暂以破解最普通的md5hash为例）</p>
<h5 id="纯字典模式（Straight）"><a href="#纯字典模式（Straight）" class="headerlink" title="纯字典模式（Straight）"></a>纯字典模式（Straight）</h5><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">hashcat64 -o res.txt -a 0 -m 0 mima.txt cain.txt</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571384854383.png" alt="1571384854383"></p>
<p>破解成功！查看密码</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571384886260.png" alt="1571384886260"></p>
<h5 id="智能字典爆破（Combination）"><a href="#智能字典爆破（Combination）" class="headerlink" title="智能字典爆破（Combination）"></a>智能字典爆破（Combination）</h5><p>需要事前把密码中有可能包含到的单词都放到字典中，比如，我有两个这样的字典，字典包含如下字符串，而我实际的密码是adminpass[字典文件可同时有很多个，不过那也意味你的组合也会特别多，速度就会慢]，实际破解就可以像下面就这样写</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571386115712.png" alt="1571386115712"></p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571386127521.png" alt="1571386127521"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">hashcat64 -a 1 -m 0 hash.txt dict1.txt dict2.txt</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571386339462.png" alt="1571386339462"></p>
<h5 id="纯掩码爆破（Brute-force）"><a href="#纯掩码爆破（Brute-force）" class="headerlink" title="纯掩码爆破（Brute-force）"></a>纯掩码爆破（Brute-force）</h5><p>纯掩码的爆破模式，其实就是尝试逐个位破解，一般人的密码设置习惯大都是基于大小写字母数字这种形式，如果hash实在比较多，可以尝试从指定的位数开始一位位的将大小写特殊字符轮询，这跑，可以节省一些时间；密码为roottoor，8位小写字母</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">hashcat64 -a 3 -m 0 hash.txt ?l?l?l?l?l?l?L?l</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571387130517.png" alt="1571387130517"></p>
<p>破解成功，获得hash值</p>
<h5 id="字典-纯掩码配合（Hybrid-Wordlist-Mask）"><a href="#字典-纯掩码配合（Hybrid-Wordlist-Mask）" class="headerlink" title="字典+纯掩码配合（Hybrid Wordlist + Mask）"></a>字典+纯掩码配合（Hybrid Wordlist + Mask）</h5><p>把可能存在的字符串事先写到字典中，然后hashcat在破解的时候会把后面所有的掩码组合跟前面的字典每行中的字符串进行拼接，直到猜解出明文，下面（7）也是一样，只不过下面的是把掩码放在了前面，字典放在了后面</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">dict1.txt中的内容是这样的</span><br><span class="line">admin</span><br><span class="line">root</span><br><span class="line">....</span><br></pre></td></tr></table></figure>

<p>实际的明文密码是这样的</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">adminpass123</span><br></pre></td></tr></table></figure>

<p>实际破解的掩码，就可以这样写</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">hashcat64 -m 0 hash.txt -a 6 dict.txt -1 ?l ?l?1?1?1?d?d?d</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1571465471087.png" alt="1571465471087"></p>
<p>破解成功！</p>

          
        
      
    </div>
    
    
    

    

    

    

    <footer class="post-footer">
      

      

      

      
      
        <div class="post-eof"></div>
      
    </footer>
  </div>
  
  
  
  </article>


    
      

  

  
  
  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="https://gitee.com/blghost/blog.git/blog/2019/09/26/认识sql并学习数据库的基础操作/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="Blackghost">
      <meta itemprop="description" content>
      <meta itemprop="image" content="/blog/images/avatar.gif">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Hexo">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
                
                <a class="post-title-link" href="/blog/2019/09/26/认识sql并学习数据库的基础操作/" itemprop="url">认识SQL并学习数据库的基础操作</a></h1>
        

        <div class="post-meta">
          <span class="post-time">
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              
              <time title="创建于" itemprop="dateCreated datePublished" datetime="2019-09-26T00:13:08+08:00">
                2019-09-26
              </time>
            

            

            
          </span>

          

          
            
          

          
          

          

          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        
          
            <h2 id="目的："><a href="#目的：" class="headerlink" title="目的："></a>目的：</h2><ol>
<li>认识什么是SQL</li>
<li>认识什么是关系型数据库和非关系型数据库，两者都包含哪些种类的数据库；并且理解两者之间的区别。</li>
<li>学习一种关系型数据库的操作，包括（对数据库和数据表的增、删、改、查）。</li>
</ol>
<h2 id="背景知识："><a href="#背景知识：" class="headerlink" title="背景知识："></a>背景知识：</h2><h3 id="SQL简介"><a href="#SQL简介" class="headerlink" title="SQL简介"></a>SQL简介</h3><p>​        SQL结构化查询语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。</p>
<h3 id="关系型数据库和非关系型数据库"><a href="#关系型数据库和非关系型数据库" class="headerlink" title="关系型数据库和非关系型数据库"></a>关系型数据库和非关系型数据库</h3><h4 id="关系型数据库"><a href="#关系型数据库" class="headerlink" title="关系型数据库"></a>关系型数据库</h4><p>​        关系型数据库是指采用了关系模型来组织数据的数据库，关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组。<strong>常见的关系型数据库有：Oracle、SQLServer、DB2、Mysql。</strong></p>
<h4 id="非关系型数据库"><a href="#非关系型数据库" class="headerlink" title="非关系型数据库"></a>非关系型数据库</h4><p>​        非关系型数据库NoSQL是没有使用关系模型组织数据的数据库，数据之间无关系性，非关系数据库针对不同的数据的特点以不同的方式存储数据，常见的有如下：<strong>键值、列存储、文档型、图形。</strong></p>
<hr>
<h5 id="键值存储数据库："><a href="#键值存储数据库：" class="headerlink" title="键值存储数据库："></a>键值存储数据库：</h5><p>​        这一类数据库主要会使用一个哈希表，表中有一个特定的键和一个指针指向特定的数据。如：<strong><em>Aerospike, Apache Ignite, ArangoDB, Berkeley DB, Couchbase, Dynamo, FoundationDB, InfinityDB, MemcacheDB, MUMPS, Oracle NoSQL Database, OrientDB, Redis, Riak, SciDB, SDBM/Flat File dbm,ZooKeeper。</em></strong></p>
<hr>
<h5 id="列式存储数据库："><a href="#列式存储数据库：" class="headerlink" title="列式存储数据库："></a>列式存储数据库：</h5><p>​        列式数据库是以列相关存储架构进行数据存储的数据库，主要适合于批量数据的处理和即时查询。常见的有：<strong><em>Accumulo, Cassandra, Scylla, HBase。</em></strong></p>
<hr>
<h5 id="文档数据库："><a href="#文档数据库：" class="headerlink" title="文档数据库："></a>文档数据库：</h5><p>​        文档数据库区别于传统的其它数据库，它是用来管理文档；一个文档相当于关系数据库中的一条记录。使用该类型的数据库有：<strong><em>Apache CouchDB, ArangoDB, BaseX, Clusterpoint, Couchbase, Cosmos DB, IBM Domino, MarkLogic, MongoDB, OrientDB, Qizx, RethinkDB</em></strong></p>
<hr>
<h5 id="图形数据库："><a href="#图形数据库：" class="headerlink" title="图形数据库："></a>图形数据库：</h5><p>​        图形数据库是NoSQL数据库的一种类型，它应用图形理论存储实体之间的关系信息。图形数据库是一种非关系型数据库，它应用图形理论存储实体之间的关系信息。用该类型的数据库有：<strong><em>AllegroGraph, ArangoDB, InfiniteGraph, Apache Giraph, MarkLogic, Neo4J, OrientDB, Virtuoso</em></strong></p>
<hr>
<h4 id="两者之间的区别"><a href="#两者之间的区别" class="headerlink" title="两者之间的区别"></a><strong>两者之间的区别</strong></h4><p><strong>关系型数据库：</strong></p>
<table>
<thead>
<tr>
<th align="center">优点</th>
<th align="center">缺点</th>
</tr>
</thead>
<tbody><tr>
<td align="center">易于理解：结构化的数据存储（行列表）</td>
<td align="center">读写性能差，在大数据环境下更为明显</td>
</tr>
<tr>
<td align="center">便于操作：通用的SQL语言</td>
<td align="center">采用固定的表结构，导致可扩展性差</td>
</tr>
<tr>
<td align="center">实现单表及多表的复杂查询</td>
<td align="center"></td>
</tr>
</tbody></table>
<p><strong>非关系型数据库：</strong></p>
<table>
<thead>
<tr>
<th align="center">优点</th>
<th align="center">缺点</th>
</tr>
</thead>
<tbody><tr>
<td align="center">数据存储格式多样、灵活、扩展性强</td>
<td align="center">没有统一的数据管理语言，学习成本高</td>
</tr>
<tr>
<td align="center">读写速度快</td>
<td align="center">不支持多表的查询和操作</td>
</tr>
<tr>
<td align="center">部署和维护成本低</td>
<td align="center">对数据的管理不方便</td>
</tr>
</tbody></table>
<h2 id="数据库管理的基本操作"><a href="#数据库管理的基本操作" class="headerlink" title="数据库管理的基本操作"></a>数据库管理的基本操作</h2><h3 id="以Root身份登录MySQL"><a href="#以Root身份登录MySQL" class="headerlink" title="以Root身份登录MySQL"></a>以Root身份登录MySQL</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql -u root -p 密码</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570340994643.png" alt="1570340994643"></p>
<h3 id="创建新用户"><a href="#创建新用户" class="headerlink" title="创建新用户"></a>创建新用户</h3><p>添加一条记录，用户名为guest，密码为guest123,并授权SELECT、INSERT、UPDATE权限</p>
<h4 id="方法一："><a href="#方法一：" class="headerlink" title="方法一："></a>方法一：</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">CREATE USER &apos;newuser&apos;@&apos;localhost&apos; #@后面跟着的是主机名，可以是远程服务器:www.server.com</span><br><span class="line">  IDENTIFIED BY &apos;user-password&apos;;</span><br><span class="line">GRANT INSERT,SELECT     #用户的权限，这里分配了插入和选择缺陷，使用所有权限 ALL（不推荐）</span><br><span class="line">  ON *.*				#全部数据库和表，可以指定数据库和表：database.* 或者 db.tablename</span><br><span class="line">  TO &apos;newuser&apos;@&apos;localhost&apos;</span><br><span class="line">  WITH GRANT OPTION; #具有授于其它用户权限的能力，授予权限仅限于自己被授予的权限，一般不用该选项</span><br></pre></td></tr></table></figure>

<h4 id="方法二："><a href="#方法二：" class="headerlink" title="方法二："></a>方法二：</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">insert into user (host,user,password,select_priv,insert_priv,update_priv)values(&apos;localhost&apos;,&apos;guest&apos;,passwrod(&apos;guest123&apos;),&apos;y&apos;,&apos;y&apos;,&apos;y&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570341400561.png" alt="1570341400561"></p>
<p>查看是否创建成功！</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570341415611.png" alt="1570341415611"></p>
<p>通用的步骤（新用户的授权信息更新）：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">FLUSH PRIVILEGES;</span><br></pre></td></tr></table></figure>

<h4 id="注意事项："><a href="#注意事项：" class="headerlink" title="注意事项："></a>注意事项：</h4><ol>
<li><p>在MySQL5.7中user表的password已换成了authentication_string。</p>
</li>
<li><p>Password()加密函数已经在8.0.11中移除了，可以使用md5()函数代替。在添加新用户的时候需要执行flush privileges语句。这个命令执行后会重新载入授权表。如果你不使用该命令，你就无法使用新创建的用户来连接mysql服务器，除非重启mysql服务器。</p>
</li>
</ol>
<hr>
<h3 id="查看数据库信息"><a href="#查看数据库信息" class="headerlink" title="查看数据库信息"></a>查看数据库信息</h3><h4 id="在MySQL中查看，当前所有的用户和连接的主机和密码"><a href="#在MySQL中查看，当前所有的用户和连接的主机和密码" class="headerlink" title="在MySQL中查看，当前所有的用户和连接的主机和密码"></a>在MySQL中查看，当前所有的用户和连接的主机和密码</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select host,user,password from mysql.user;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570342162845.png" alt="1570342162845"></p>
<h4 id="查看当前用户"><a href="#查看当前用户" class="headerlink" title="查看当前用户"></a>查看当前用户</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select user();</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570342255410.png" alt="1570342255410"></p>
<h4 id="查看所有数据库"><a href="#查看所有数据库" class="headerlink" title="查看所有数据库"></a>查看所有数据库</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show databases;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570342332783.png" alt="1570342332783"></p>
<h4 id="查看数据库中所有表"><a href="#查看数据库中所有表" class="headerlink" title="查看数据库中所有表"></a>查看数据库中所有表</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show tables from testdb;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570342609678.png" alt="1570342609678"></p>
<p>或者先使用testdb这个数据库，然后再使用show tables这个命令。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">use testdb;</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show tables;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570342751654.png" alt="1570342751654"></p>
<h4 id="查看表的信息"><a href="#查看表的信息" class="headerlink" title="查看表的信息"></a>查看表的信息</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">desc test1;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570342861670.png" alt="1570342861670"></p>
<h4 id="查看当前用户权限"><a href="#查看当前用户权限" class="headerlink" title="查看当前用户权限"></a>查看当前用户权限</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show GRANTS;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570342978908.png" alt="1570342978908"></p>
<h4 id="查看MySQL版本"><a href="#查看MySQL版本" class="headerlink" title="查看MySQL版本"></a>查看MySQL版本</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select @@version</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570343040604.png" alt="1570343040604"></p>
<h3 id="数据库操作"><a href="#数据库操作" class="headerlink" title="数据库操作"></a>数据库操作</h3><h4 id="库操作"><a href="#库操作" class="headerlink" title="库操作"></a>库操作</h4><p>创建一个名为test的数据库，并且使用它。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">create database test</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">use test</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570343801463.png" alt="1570343801463"></p>
<p>删除数据库</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">drop database test;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570343872549.png" alt="1570343872549"></p>
<h4 id="表操作"><a href="#表操作" class="headerlink" title="表操作"></a>表操作</h4><p>创建一个表</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">create table table_name(</span><br><span class="line">	column1 数据类型[约束],</span><br><span class="line">	column2 数据类型[约束],</span><br><span class="line">	……</span><br><span class="line">)</span><br></pre></td></tr></table></figure>

<p>约束可选项，SQL约束和MySQL数据类型见附录。</p>
<p>实例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">create table test1(</span><br><span class="line">	id int not null primary key auto_increment,</span><br><span class="line">	name varchar(20) not null,</span><br><span class="line">	time datetime </span><br><span class="line">);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570345753835.png" alt="1570345753835"></p>
<p>删除表</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">drop table runoob;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570346198941.png" alt="1570346198941"></p>
<p>修改表</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">alter table 表名 add 列名 类型;#添加一列</span><br><span class="line">alter table 表名 drop 列名; #删除一列</span><br><span class="line">alter table 表名 modify column 列名 类型 #修改列的数据类型</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570346249772.png" alt="1570346249772"></p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570346277790.png" alt="1570346277790"></p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570346315202.png" alt="1570346315202"></p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570346333330.png" alt="1570346333330"></p>
<h4 id="数据的操作"><a href="#数据的操作" class="headerlink" title="数据的操作"></a>数据的操作</h4><p>增加一条数据</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">insert into test1(id,time,sex)values(1,&apos;2019-10-6&apos;,&apos;ma&apos;);</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570346567689.png" alt="1570346567689"></p>
<p>查询数据</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select * from test1</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570346615715.png" alt="1570346615715"></p>
<p>更改一条数据</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">update test1 set sex=&apos;v1&apos; where id =1;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570346892515.png" alt="1570346892515"></p>
<p>删除一条数据</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">delete from test1 where id =1;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1570347113403.png" alt="1570347113403"></p>
<h4 id="补充"><a href="#补充" class="headerlink" title="补充"></a>补充</h4><p>​        where子句：用来查询语句中，你可以使用一个或多个表，表之间使用逗号，分割，并使用WHERE语句来设定查询条件；可以使用AND或OR指定一个或多个条件。WHERE子句也可以运用于SQL的DELETE或者UPDATE命令。</p>
<p>​        like子句：有时候我们需要查询字段中的某段记录的字符，这时我们就需要在where子句中使用like子句。子句中使用百分号%字符来表示任意字符。like匹配/模糊匹配使用方法如下：</p>
<table>
<thead>
<tr>
<th align="center"><strong>“%a” //以a结尾的数据</strong></th>
<th align="center">“下划线（—）a下划线（—）”    //三位且中间字母是a</th>
</tr>
</thead>
<tbody><tr>
<td align="center"><strong>“a%” //以a开头的数据</strong></td>
<td align="center"><strong>“下划线（—）a”    //两位且结尾字母是a</strong></td>
</tr>
<tr>
<td align="center"><strong>“%a%” //含有a的数据</strong></td>
<td align="center"><strong>“a下划线（—）”    //两位且开头字母是a</strong></td>
</tr>
</tbody></table>
<p>​        union操作符：是用于连接两个以上的SELECT语句的结果组合到一个结果集合中。多个select语句会删除重复的数据Union后面的（两个可选参数DISTINCT：可选，删除结果中的重复数据。默认是这个；ALL：可选，返回结果中的所有数据，包括重复的。）</p>
<h3 id="附录"><a href="#附录" class="headerlink" title="附录"></a>附录</h3><h4 id="MySQL数据类型"><a href="#MySQL数据类型" class="headerlink" title="MySQL数据类型"></a>MySQL数据类型</h4><p>Mysql中定义数据字段的类型对你数据库的优化是非常重要的。</p>
<p>MySQL支持多种数据类型，大致可以分为三类：数值、日期/时间和字符串（字符）类型。</p>
<h5 id="数值类型："><a href="#数值类型：" class="headerlink" title="数值类型："></a><strong>数值类型：</strong></h5><p>MySQL支持所有标准SQL数值数据类型。</p>
<p>这些类型包括严格数值数据类型（INTEGER、SMALLINT、DECIMAL和NUMERIC），以及近似数值数据类型（FLOAT、REAL和DOUBLe precision）。</p>
<p>关键字INT是INTEGER的同义词，关键词DEC是DECIMAL的同义词BIT数据类型保存字段值，并且支持MyISAM、MEMORY、InNoDB和BDB表</p>
<p>作为SQL标准的扩展，MYSQL也支持整数类型TINYINT、MEDIUMINT和BIGINT下面的表显示了需要的每个整数类型的存储和范围。</p>
<p><img src="file:///C:%5CUsers%5Cadmin%5CAppData%5CLocal%5CTemp%5Cksohtml8744%5Cwps1.jpg" alt="img"> </p>
<h5 id="日期和时间类型："><a href="#日期和时间类型：" class="headerlink" title="日期和时间类型："></a><strong>日期和时间类型：</strong></h5><p>表示时间值的日期和时间类型为DATATIME、DATE、TIMESTAMP、TIME和YEAR</p>
<p>每个时间类型有一个有效值范围和一个‘零’值，当指定不合法的MySQL不能表示的值使用‘零’值。TIMESTAMP类型有专有的自动更新特性，将在后面描述。</p>
<p><img src="file:///C:%5CUsers%5Cadmin%5CAppData%5CLocal%5CTemp%5Cksohtml8744%5Cwps2.jpg" alt="img"> </p>
<h5 id="字符串类型："><a href="#字符串类型：" class="headerlink" title="字符串类型："></a><strong>字符串类型：</strong></h5><p>字符串类型指：char、varchar、binary、varbinary、blob、text、enum和set。该节描述了这些类型如何工作以及如何在查询中使用这些类型。</p>
<p><img src="file:///C:%5CUsers%5Cadmin%5CAppData%5CLocal%5CTemp%5Cksohtml8744%5Cwps3.jpg" alt="img"> </p>
<p>Char和varchar类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。</p>
<p>BINARY和VARBINARY类似于char和varchar，不同的是它们包含二进制字符串而不要非二进制字符串。它们包含字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值</p>
<p>BLOB是一个二进制大对象可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们区别在于可容纳存储范围不同。</p>
<p>有4中TEXT类型：TINYTEXT、TEXT、MEDIUTEXT和LONGTEXT。对应的这4种BLOB类型，可存储的最大长度不同，可根据实际情况选择。</p>
<h4 id="RDBMS术语"><a href="#RDBMS术语" class="headerlink" title="RDBMS术语"></a><strong>RDBMS术语</strong></h4><p>在开始学习MySQL数据库前，先了解一些术语：</p>
<p>数据库：数据库是一些关联表的集合，可以理解为装数据表的一个盒子</p>
<p>数据表：表示数据的矩阵。在一个数据库中变的表看起来像一个简单的电子表格</p>
<p>列：一列（数据元素）包含了相同类型的数据，例如邮政编码的数据。</p>
<p>行：一行（=元祖，或记录）是一组相关的数据，例如一条用户订阅的数据。</p>
<p>冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全。</p>
<p>主键：主键是唯一的。一个数据表中只能包含一个主键，你可以使用主键来查询数据。</p>
<p>外键：外键用于关联两个表。</p>
<p>复合键：复合键又称（组合键）将多个列作为一个索引键，一般用于复合索引。</p>
<p>索引：使用索引可以快速访问数据库表中的特定信息。索引是对数据库表中一列或者多列的值进行排序的一种结构。类似于书籍的目录。</p>
<p>参照完整性：参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。</p>
<p><img src="file:///C:%5CUsers%5Cadmin%5CAppData%5CLocal%5CTemp%5Cksohtml8744%5Cwps4.jpg" alt="img"> </p>
<p>表头：每一列的名称</p>
<p>列：具有相同数据类型的数据的集合</p>
<p>行：每一行用来描述某条记录的具体信息</p>
<p>值：行的具体信息，每个值必须与该列的数据类型相同</p>
<p>键：键的值在当前列中具有唯一性。</p>
<h3 id="参考连接："><a href="#参考连接：" class="headerlink" title="参考连接："></a>参考连接：</h3><p><a href="https://baike.baidu.com/item/%E5%85%B3%E7%B3%BB%E5%9E%8B%E6%95%B0%E6%8D%AE%E5%BA%93/8999831?fr=aladdin" target="_blank" rel="noopener">关系型数据库</a></p>
<p><a href="https://baike.baidu.com/item/NoSQL/8828247?fromtitle=%E9%9D%9E%E5%85%B3%E7%B3%BB%E5%9E%8B%E6%95%B0%E6%8D%AE%E5%BA%93&fromid=6376860&fr=aladdin" target="_blank" rel="noopener">非关系型数据库</a></p>
<p><a href="https://www.runoob.com/mysql/mysql-tutorial.html" target="_blank" rel="noopener">MySQL数据库</a></p>

          
        
      
    </div>
    
    
    

    

    

    

    <footer class="post-footer">
      

      

      

      
      
        <div class="post-eof"></div>
      
    </footer>
  </div>
  
  
  
  </article>


    
      

  

  
  
  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="https://gitee.com/blghost/blog.git/blog/2019/09/04/环境搭建/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="Blackghost">
      <meta itemprop="description" content>
      <meta itemprop="image" content="/blog/images/avatar.gif">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Hexo">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
                
                <a class="post-title-link" href="/blog/2019/09/04/环境搭建/" itemprop="url">成长日记第一篇</a></h1>
        

        <div class="post-meta">
          <span class="post-time">
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              
              <time title="创建于" itemprop="dateCreated datePublished" datetime="2019-09-04T21:56:03+08:00">
                2019-09-04
              </time>
            

            

            
          </span>

          

          
            
          

          
          

          

          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        
          
            <h2 id="背景目的"><a href="#背景目的" class="headerlink" title="背景目的"></a>背景目的</h2><ul>
<li><p>基于LEMP栈 搭建本地Web服务器，实现一个能在本地访问网页，网页中实现对数据库的简单操作。</p>
</li>
<li><p>学习目标：</p>
</li>
<li><p>了解什么是LEMP</p>
</li>
<li><ol>
<li>LEMP是PHP网站应用的运行环境。</li>
<li>L表示Linux，它是一种操作系统，比如我们自家用的Windows系统。可以当做一个地球</li>
<li>E表示Ngix，这个E取的是Nginx的读音（Engine X）它是一种Web服务器。可以当做容器</li>
<li>M表示MySQL或者MariaDB是网站应用使用的数据库，这里选择MySQL，这里可以比作一个人的大脑</li>
<li>P表示哦PHP。它是解析PHP语言的解析器，可以使PHP-FPM跟Web服务相互沟通，然后会把最后结果发回给用户，这里比作人。</li>
</ol>
</li>
<li><p>理解Web服务器的工作原理，学会手动搭建一个Web服务器</p>
</li>
<li><p>学习简单的HTML和PHP知识，并编写网站前端和服务端的代码</p>
</li>
<li><p>学习MySql数据库的基本知识，完成数据库的基本操作，实现PHP和MySQL连接</p>
</li>
<li><p>学习如何加固LEMP栈</p>
</li>
</ul>
<h2 id="环境搭建"><a href="#环境搭建" class="headerlink" title="环境搭建"></a>环境搭建</h2><p>在linux系统安装Nginx、php-fpm和mysql软件，测试系统：Linux+Nginx+php-fpm+mysql</p>
<h3 id="安装Linux系统"><a href="#安装Linux系统" class="headerlink" title="安装Linux系统"></a><strong>安装Linux系统</strong></h3><p>Linux系统比较多类型，我这里就选用Centos7版本，使用VmWware安装虚拟机，来安装使用，安装完成后可以使用，具体可以百度，这里就不讲解中间过程</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">cat  /etc/centos-release</span><br></pre></td></tr></table></figure>

<p>来查看系统的版本</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568623495939.png" alt="1568623495939"></p>
<hr>
<h3 id="安装Nginx"><a href="#安装Nginx" class="headerlink" title="安装Nginx"></a>安装Nginx</h3><p>首先，安装Ngxinx需要安装它的依赖文件，所以我这里使用Centos中的yum源来安装Nginx所依赖的库文件。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">yum install gcc gcc-c++ openssl openssl-devel cyrus-sasl-md5</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568624392904.png" alt="1568624392904"></p>
<p>然后同意输入Y，然后提示，完毕！后安装成功</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568624425948.png" alt="1568624425948"></p>
<p>然后我们在/usr/local下面新建一个software文件夹，并且切换到目录下</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mkdir /usr/local/software</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">cd /usr/local/software</span><br></pre></td></tr></table></figure>

<p>   <img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568624855615.png" alt="1568624855615"></p>
<p>下载Nginx配置文件pcre、zlib和openssl包。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">wget https://sourceforge.net/projects/pcre/files/pcre/8.43/pcre-8.43.tar.gz</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">wget http://zlib.net/zlib-1.2.11.tar.gz</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">wget http://www.openssl.org/source/openssl-1.1.1c.tar.gz</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568626307728.png" alt="1568626307728"></p>
<p>下载完安装包后，下载Nginx安装包</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">wget http://nginx.org/download/nginx-1.16.0.tar.gz</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568625760920.png" alt="1568625760920"></p>
<p>下载完成后，解压到当前的目录中，</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">tar -zxvf nginx-1.16.0.tar.gz</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568625804952.png" alt="1568625804952"></p>
<p>解压完成后，可以使用ls命令查看当前目录下的所有文件</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ls</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568626637380.png" alt="1568626637380"></p>
<p>然后切换到nginx目录下</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">cd nginx</span><br></pre></td></tr></table></figure>

<p>然后再配置pcre和zlib、openssl库文件，需要注意的是pcre、zlib和openssl都必须是跟文件夹的名称一致</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">./configure --prefix=/usr/local/nginx --with-http_ssl_module --with-pcre=../pcre-8.43 --with-zlib=../zlib-1.2.11 --with-openssl=../openssl-1.1.1c</span><br></pre></td></tr></table></figure>

<p>配置完成后，看到有这个提示！</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568627876763.png" alt="1568627876763"></p>
<p>然后再安装文件文件</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">make &amp;&amp; make install</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568628315552.png" alt="1568628315552"></p>
<p>安装完成后会有提示离开目录，然后测试一下是否成功！直接访问启动文件</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">/usr/local/nginx/sbin/nginx</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568628964972.png" alt="1568628964972"></p>
<p>然后什么都没提示，然后打开浏览器访问127.0.0.1</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568629034047.png" alt="1568629034047"></p>
<p>提示Welcome to nginx，表示我们已经安装成功了，接下来我们安装php-fpm</p>
<hr>
<h3 id="安装php-fpm"><a href="#安装php-fpm" class="headerlink" title="安装php-fpm"></a>安装php-fpm</h3><p>第一步还是安装php-fpm的依赖库文件</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">yum install gcc gcc-c++ libxml2 libxml2-devel autoconf libjpeg libjpeg-devel libpng libpng-devel freetype freetype-devel zlib zlib-devel glibc glibc-devel glib2 glib2-devel</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568630296484.png" alt="1568630296484"></p>
<p>然后提示是否安装文件，选择y安装，然后就一直等待安装，安装成功后提示下面的图片</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568630380700.png" alt="1568630380700"></p>
<p>然后下载，php-fpm文件包，</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">wget https://www.php.net/distributions/php-7.3.7.tar.gz</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568630638876.png" alt="1568630638876"></p>
<p>下载完成后就会有如上图提示，然后把文件解压出来，并且配置一下文件</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">tar -zxvf php-7.3.7.tar.gz</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568630769802.png" alt="1568630769802"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">cd php-7.3.7</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">./configure --prefix=/usr/local/php-fpm --enable-fpm --with-zlib  --with-pdo-mysql --enable-mbstring --with-gd --with-png-dir=/usr/lib64 --with-jpeg-dir=/usr/lib64 --with-freetype-dir=/usr/lib64</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568631040872.png" alt="1568631040872"></p>
<p>配置完成后，会有上图的提示，接下来安装文件</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">make &amp;&amp; make install</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568631135707.png" alt="1568631135707"></p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568634125679.png" alt="1568634125679"></p>
<p>完成安装后，测试下能否解析PHP的网页，到/usr/local/php-fpm/sbin/php-fpm启动文件</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568634354126.png" alt="1568634354126"></p>
<p>提示配置文件没找到，然后我们去/usr/local/php-fpm/etc/复制一份配置文件出来，并且修改php-fpm.conf</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">cp php-fpm.conf.default php-fpm.conf</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">vim php-fpm.conf</span><br></pre></td></tr></table></figure>

<p>找到下面这一行并且去掉前面的分号（;）</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">pid = run/php-fpm.pid</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568634859564.png" alt="1568634859564"></p>
<p>然后再尝试一下启动，发现还是有错误</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568636448384.png" alt="1568636448384"></p>
<p>然后发现问题出现在php.fpm.conf文件夹的第143行那里。查找发现在etc/php-fpm.d的目录下没有conf文件，尝试把<a href="http://www.conf.default文件复制一份" target="_blank" rel="noopener">www.conf.default文件复制一份</a></p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568640796234.png" alt="1568640796234"></p>
<p>然后再开启，发现成功了！然后在服务器的网站根目录新建一个php网页文件（/usr/local/nginx/html/）</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568641116815.png" alt="1568641116815"></p>
<p>重新启动服务器文件，重启完后重新访问127.0.0.1</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">nginx -s reload</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568641243073.png" alt="1568641243073"></p>
<p>然后尝试把index.html移除，移除后出现新的问题，状态码403</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568641317767.png" alt="1568641317767"></p>
<p>使用cat的命令查看nginx的日志</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">cat /usr/local/nginx/logs/error.log</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568649724763.png" alt="1568649724763"></p>
<p>发现，权限不够，然后去修改一下服务器的nginx配置文件，</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">vim /usr/local/nginx/conf/nginx.conf</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568650139310.png" alt="1568650139310"></p>
<p>复制配置文件的.php文件到/下面，然后修改fastcgi_param中的值，修改成$document_root；然后再index加上index.php默认的配置文件，保存并退出！然后重启一下服务器。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">killall nginx</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">/usr/local/nginx/sbin/nginx</span><br></pre></td></tr></table></figure>

<p>刷新下页面发现可以解析成功了！</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568650336470.png" alt="1568650336470"></p>
<hr>
<h3 id="安装Mysql"><a href="#安装Mysql" class="headerlink" title="安装Mysql"></a>安装Mysql</h3><p>安装mysql前，先下载安装包</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568689143836.png" alt="1568689143836"></p>
<p>下载完成后我们使用yum来安装rpm包，因为要有一个是服务器所以需要下载下面那个</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">yum install mysql57-community-release-el7-10.noarch.rpm</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">yum install mysql-community-server</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568689310643.png" alt="1568689310643"></p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568691247508.png" alt="1568691247508"></p>
<p>然后测试下能否连接到数据库</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568692173474.png" alt="1568692173474"></p>
<p>发现无法连接，然后重置下root的密码，</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">vim /etc/my.cnf</span><br></pre></td></tr></table></figure>

<p>在最后面添加skip-grant-tables，完成后启动mysql服务器</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568693282614.png" alt="1568693282614"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">service mysqld start</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568693402854.png" alt="1568693402854"></p>
<p>成功后，我们输入mysql指令</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568693446191.png" alt="1568693446191"></p>
<p>先查看下有什么数据库存在当前环境下，</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show databases;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568693518134.png" alt="1568693518134"></p>
<p>使用mysql这个数据库，在来查看下数据库下存在有什么表。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">use mysql</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show tables;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568693595122.png" alt="1568693595122"></p>
<p>然后使用user表中的数据，并且修改下user表中的root密码。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select user,host from user;</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568693735909.png" alt="1568693735909"></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">update mysql.user set authentication_string =password(‘123’) where user=‘root’</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568694367406.png" alt="1568694367406"></p>
<p>然后再使用下面这条命令在次更新下：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ALTER USER &apos;root&apos;@&apos;localhost&apos; IDENTIFIED BY &apos;新密码&apos;;</span><br></pre></td></tr></table></figure>

<p>然后去掉mysql配置文件中的最后一行，并且尝试用root连接。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql -u root -p</span><br></pre></td></tr></table></figure>

<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568694548998.png" alt="1568694548998"></p>
<p>连接成功！！</p>
<hr>
<h3 id="问题与解决方法"><a href="#问题与解决方法" class="headerlink" title="问题与解决方法"></a>问题与解决方法</h3><p>问题1：为什么在服务器本机能访问，但是使用ip地址却无法访问？</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568695280026.png" alt="1568695280026"></p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568695306034.png" alt="1568695306034"></p>
<p>答:设置防火墙允许80端口访问，然后重启下防火墙，刷新页面发现成功了</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568695513812.png" alt="1568695513812"></p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568695750388.png" alt="1568695750388"></p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1568695771436.png" alt="1568695771436"></p>
<h2 id="连接mysql数据库"><a href="#连接mysql数据库" class="headerlink" title="连接mysql数据库"></a>连接mysql数据库</h2><p>因为php7开始不支持mysql，后面查找了一些资料发现使用了Mysqli来替代了mysql所以一直连接不上，在使用前需要先安装mysqli的支持库，php安装包会自带一个mysqli的添加库，需要这样安装。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">cd /usr/local/php-7.3.7/ext/mysqli</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">./configure --with-php-config=/usr/local/php-fpm/bin/php-config --with-mysqli=/usr/local/mysql/bin/mysql_config</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">make &amp;&amp; make install</span><br></pre></td></tr></table></figure>

<p>安装完成后，编写index.php文件，并尝试从数据库中读取数据</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1569044210648.png" alt="1569044210648"></p>
<p>连接成功，并读取数据库信息！！</p>
<p><img src="C:%5CUsers%5Cadmin%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1569044104561.png" alt="1569044104561"></p>
<p>注意：连接时需要先新建一个用户、数据库、数据表和数据，并把数据库赋予该用户的权限，不建议用root用户出于安全起见。</p>

          
        
      
    </div>
    
    
    

    

    

    

    <footer class="post-footer">
      

      

      

      
      
        <div class="post-eof"></div>
      
    </footer>
  </div>
  
  
  
  </article>


    
      

  

  
  
  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="https://gitee.com/blghost/blog.git/blog/2019/09/04/Hexo+Gitee搭建博客/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="Blackghost">
      <meta itemprop="description" content>
      <meta itemprop="image" content="/blog/images/avatar.gif">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Hexo">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
                
                <a class="post-title-link" href="/blog/2019/09/04/Hexo+Gitee搭建博客/" itemprop="url">Hexo+Gitee搭建博客</a></h1>
        

        <div class="post-meta">
          <span class="post-time">
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              
              <time title="创建于" itemprop="dateCreated datePublished" datetime="2019-09-04T18:20:00+08:00">
                2019-09-04
              </time>
            

            

            
          </span>

          

          
            
          

          
          

          

          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        
          
            <h2 id="前提："><a href="#前提：" class="headerlink" title="前提："></a>前提：</h2><p>搭建一个个人的搭建博客。</p>
<h2 id="需求分析："><a href="#需求分析：" class="headerlink" title="需求分析："></a>需求分析：</h2><p>由于本人的经济状况，所以这里就没有Q去购买一个云服务，然后通过百度找到使用Hexo+Github可以搭建一个免费的个人博客。然后考虑Github平时下载和上传的速度，决定使用比较靠谱的Gitee，毕竟到时候Github被墙了还能补救嘛~</p>
<h2 id="环境背景："><a href="#环境背景：" class="headerlink" title="环境背景："></a>环境背景：</h2><p><strong>Hexo:</strong> 是一款基于Node.js用于快速搭建博客的框架，它会帮你构建整个博客的整个框架，你只需要会使用Markdown写博客即可。</p>
<p><strong>Gitee:</strong>码云(<em>gitee</em>.com)是 OSCHINA.NET 推出的代码托管平台,支持 Git 和 SVN,提供免费的私有仓库托管。</p>
<h2 id="环境搭建："><a href="#环境搭建：" class="headerlink" title="环境搭建："></a>环境搭建：</h2><p>目标环境：win7+Node.js+Hexo+Git</p>
<p>Node.js: <a href="http://nodejs.cn/download/" target="_blank" rel="noopener">http://nodejs.cn/download/</a></p>
<p>下载并安装。</p>
<p>查看是否安装成功</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">npm --version</span><br></pre></td></tr></table></figure>

<p>安装<strong>Hexo</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">npm install hexo-cli -g</span><br></pre></td></tr></table></figure>

<p>如果出现以下问题：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">npm WARN notsup SKIPPING OPTIONAL DEPENDENCY: Unsupported platform for fsevents@1.2.4: wanted &#123;&quot;os&quot;:&quot;darwin&quot;,&quot;arch&quot;:&quot;any&quot;&#125; (current: &#123;&quot;os&quot;:&quot;win32&quot;,&quot;arch&quot;:&quot;x64&quot;&#125;)</span><br></pre></td></tr></table></figure>

<p><strong>可以直接忽略，系统会自动安装</strong></p>
<p>查看Hexo是否安装成功</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">hexo --version</span><br></pre></td></tr></table></figure>

<p>Git: <a href="https://pc.qq.com/detail/13/detail_22693.html" target="_blank" rel="noopener">https://pc.qq.com/detail/13/detail_22693.html</a></p>
<p>下载并安装。</p>
<p>成功后会在鼠标-&gt;右键多了Git两项的选项</p>
<h2 id="搭建博客"><a href="#搭建博客" class="headerlink" title="搭建博客:"></a>搭建博客:</h2><p>1、在你的电脑上创建<strong>BLOG</strong>文件夹，在BLOG文件夹右键<strong>GIT Bash Here</strong>，输入命令</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">hexo init</span><br></pre></td></tr></table></figure>

<p>2、然后再输入命令克隆hexo主题：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">git clone https://github.com/theme-next/hexo-theme-next</span><br></pre></td></tr></table></figure>

<p><a href="https://hexo.io/themes/" target="_blank" rel="noopener">查看更多主题</a></p>
<p>3、_config.yml对博客进行基础配置</p>
<p><img src="https://i.loli.net/2019/09/10/7f1eWBu8zVvHX2n.png" alt="1567591218342.png"></p>
<p><img src="https://i.loli.net/2019/09/10/dt1iDNlpxfSzJOb.png" alt="1567591379568.png"></p>
<p>title：标题</p>
<p>author: 用户名</p>
<p>language: 语言</p>
<p>theme: 主题包的名称</p>
<p>修改完后保存，<strong>注意：每个属性后面都要有个空格</strong></p>
<p>4、本地预览博客</p>
<p>编译项目：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">hexo g</span><br></pre></td></tr></table></figure>

<p>运行项目：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">hexo s</span><br></pre></td></tr></table></figure>

<p>然后在浏览器访问：localhost:4000 就可以看到最终效果了</p>
<h2 id="部署博客到Gitee上"><a href="#部署博客到Gitee上" class="headerlink" title="部署博客到Gitee上:"></a>部署博客到Gitee上:</h2><p>1、在博客中新建仓库，仓库名为blog(自定义)；然后选择公开</p>
<p><img src="https://i.loli.net/2019/09/10/6rQH7xzEFKftnIc.png" alt="1567591722585.png"></p>
<p>2、配置下_config.yml中Git</p>
<p><img src="https://i.loli.net/2019/09/10/H6Jg4F89lOI3uSm.png" alt="1567591810774.png"></p>
<p>3、发布到Gitee</p>
<p>输入命令：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">npm install hexo-deployer-git --save</span><br></pre></td></tr></table></figure>

<p>安装自动部署发布工具，然后再输入命令：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">hexo clean &amp;&amp; hexo g &amp;&amp; hexo d</span><br></pre></td></tr></table></figure>

<p>发布博客，首次发布需要在shell中输入账号和密码</p>
<p>4、Gitee Page设置</p>
<p>在仓库，服务中的Gitee Page（需要绑定手机才能开启），然后更新页面</p>
<p><img src="https://i.loli.net/2019/09/10/J7ebxF2E34fQCNX.png" alt="1567591952538.png"></p>
<p><img src="https://i.loli.net/2019/09/10/BDmTg19oSItlNdM.png" alt="1567591993210.png"></p>
<h2 id="写博客"><a href="#写博客" class="headerlink" title="写博客:"></a>写博客:</h2><p>1、创建一个新的页面：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">hexo new 文章名</span><br></pre></td></tr></table></figure>

<p>2、写完博文后，生成网页文件：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">hexo g</span><br></pre></td></tr></table></figure>

<p>3、发布到Gitee上</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">hexo d</span><br></pre></td></tr></table></figure>

<p><img src="https://i.loli.net/2019/09/10/pVAK2r47jfS1cNk.png" alt="1567592398703.png"></p>
<p>这里是博客地址</p>

          
        
      
    </div>
    
    
    

    

    

    

    <footer class="post-footer">
      

      

      

      
      
        <div class="post-eof"></div>
      
    </footer>
  </div>
  
  
  
  </article>


    
      

  

  
  
  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="https://gitee.com/blghost/blog.git/blog/2019/09/02/hello-world/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="Blackghost">
      <meta itemprop="description" content>
      <meta itemprop="image" content="/blog/images/avatar.gif">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Hexo">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
                
                <a class="post-title-link" href="/blog/2019/09/02/hello-world/" itemprop="url">Hello World</a></h1>
        

        <div class="post-meta">
          <span class="post-time">
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              
              <time title="创建于" itemprop="dateCreated datePublished" datetime="2019-09-02T21:57:08+08:00">
                2019-09-02
              </time>
            

            

            
          </span>

          

          
            
          

          
          

          

          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        
          
            <p>Welcome to <a href="https://hexo.io/" target="_blank" rel="noopener">Hexo</a>! This is your very first post. Check <a href="https://hexo.io/docs/" target="_blank" rel="noopener">documentation</a> for more info. If you get any problems when using Hexo, you can find the answer in <a href="https://hexo.io/docs/troubleshooting.html" target="_blank" rel="noopener">troubleshooting</a> or you can ask me on <a href="https://github.com/hexojs/hexo/issues" target="_blank" rel="noopener">GitHub</a>.</p>
<h2 id="Quick-Start"><a href="#Quick-Start" class="headerlink" title="Quick Start"></a>Quick Start</h2><h3 id="Create-a-new-post"><a href="#Create-a-new-post" class="headerlink" title="Create a new post"></a>Create a new post</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">$ hexo new <span class="string">"My New Post"</span></span><br></pre></td></tr></table></figure>

<p>More info: <a href="https://hexo.io/docs/writing.html" target="_blank" rel="noopener">Writing</a></p>
<h3 id="Run-server"><a href="#Run-server" class="headerlink" title="Run server"></a>Run server</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">$ hexo server</span><br></pre></td></tr></table></figure>

<p>More info: <a href="https://hexo.io/docs/server.html" target="_blank" rel="noopener">Server</a></p>
<h3 id="Generate-static-files"><a href="#Generate-static-files" class="headerlink" title="Generate static files"></a>Generate static files</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">$ hexo generate</span><br></pre></td></tr></table></figure>

<p>More info: <a href="https://hexo.io/docs/generating.html" target="_blank" rel="noopener">Generating</a></p>
<h3 id="Deploy-to-remote-sites"><a href="#Deploy-to-remote-sites" class="headerlink" title="Deploy to remote sites"></a>Deploy to remote sites</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">$ hexo deploy</span><br></pre></td></tr></table></figure>

<p>More info: <a href="https://hexo.io/docs/deployment.html" target="_blank" rel="noopener">Deployment</a></p>

          
        
      
    </div>
    
    
    

    

    

    

    <footer class="post-footer">
      

      

      

      
      
        <div class="post-eof"></div>
      
    </footer>
  </div>
  
  
  
  </article>


    
  </section>

  


          </div>
          


          

        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    
    <div class="sidebar-inner">

      

      

      <section class="site-overview-wrap sidebar-panel sidebar-panel-active">
        <div class="site-overview">
          <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
            
              <p class="site-author-name" itemprop="name">Blackghost</p>
              <p class="site-description motion-element" itemprop="description"></p>
          </div>

          <nav class="site-state motion-element">

            
              <div class="site-state-item site-state-posts">
              
                <a href="/blog/archives/">
              
                  <span class="site-state-item-count">6</span>
                  <span class="site-state-item-name">日志</span>
                </a>
              </div>
            

            

            
              
              
              <div class="site-state-item site-state-tags">
                
                  <span class="site-state-item-count">15</span>
                  <span class="site-state-item-name">标签</span>
                
              </div>
            

          </nav>

          

          

          
          

          
          

          

        </div>
      </section>

      

      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright">&copy; <span itemprop="copyrightYear">2019</span>
  <span class="with-love">
    <i class="fa fa-user"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">Blackghost</span>

  
</div>


  <div class="powered-by">由 <a class="theme-link" target="_blank" href="https://hexo.io">Hexo</a> 强力驱动</div>



  <span class="post-meta-divider">|</span>



  <div class="theme-info">主题 &mdash; <a class="theme-link" target="_blank" href="https://github.com/iissnan/hexo-theme-next">NexT.Muse</a> v5.1.4</div>




        







        
      </div>
    </footer>

    
      <div class="back-to-top">
        <i class="fa fa-arrow-up"></i>
        
      </div>
    

    

  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>









  












  
  
    <script type="text/javascript" src="/blog/lib/jquery/index.js?v=2.1.3"></script>
  

  
  
    <script type="text/javascript" src="/blog/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script>
  

  
  
    <script type="text/javascript" src="/blog/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>
  

  
  
    <script type="text/javascript" src="/blog/lib/velocity/velocity.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/blog/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/blog/lib/fancybox/source/jquery.fancybox.pack.js?v=2.1.5"></script>
  


  


  <script type="text/javascript" src="/blog/js/src/utils.js?v=5.1.4"></script>

  <script type="text/javascript" src="/blog/js/src/motion.js?v=5.1.4"></script>



  
  

  

  


  <script type="text/javascript" src="/blog/js/src/bootstrap.js?v=5.1.4"></script>



  


  




	





  





  












  





  

  

  

  
  

  

  

  

</body>
</html>
